package org.sapfeed.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

public class SQLDataBase {
	private int userID;
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public String addUser( String username, String password ) {
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			
			//Add new user to the database
			preparedStatement = connect.prepareStatement("INSERT INTO user SET name=?, password=?;");
			preparedStatement.setString(1,username);
			preparedStatement.setString(2,password);
			int affectedRows = preparedStatement.executeUpdate();
			
			preparedStatement = connect.prepareStatement("SELECT * from user WHERE name=? AND password =?;");
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			//Retrieve the user's id
			if(resultSet.next()) {
				String id = resultSet.getString("id");
				return "ID Created! Your User ID is " + id; 
			}
			return "Fail";
	
		} catch (Exception e) { 
			return "Cannot create user id";
		}
	}
	public int getUserConsumption( String dataSet) {
		String message;
		int numberOfMessages = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
	//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT COUNT(quantity) FROM consumption_log WHERE consumer_id = (SELECT id FROM consumer WHERE user_id=?) AND dataset_id = ? AND timestamp >= ?;");
			preparedStatement.setInt(1, userID);
			preparedStatement.setInt(2, Integer.parseInt(dataSet));
			preparedStatement.setLong(3, System.currentTimeMillis()-1000);
			resultSet = preparedStatement.executeQuery();
			
			if( resultSet.next()) {
				numberOfMessages = resultSet.getInt(1);
				if(numberOfMessages>=100){
					return 0;
				}
				message = " Login Successful ";
			} else {
				message = " Login Failed ";
			}
		} catch (Exception e) { 
		}
		return numberOfMessages;
	}
	public int getTotalMessages( String dataSet) {
		String message;
		int numberOfMessages = 0;
		try {
	//		Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
	//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT COUNT(quantity) FROM consumption_log WHERE consumer_id = (SELECT id FROM consumer WHERE user_id=?) AND dataset_id = ?;");
			preparedStatement.setInt(1, userID);
			preparedStatement.setInt(2, Integer.parseInt(dataSet));
			resultSet = preparedStatement.executeQuery();
			
			if( resultSet.next()) {
				numberOfMessages = resultSet.getInt(1);
				message = " Login Successful ";
			} else {
				message = " Login Failed ";
			}
		} catch (Exception e) { 
		}
		return numberOfMessages;
	}
	
	public int getTotalPublished( String dataSet) {
		String message;
		int numberOfMessages = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
	//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT COUNT(quantity) FROM consumption_log WHERE dataset_id = ?;");
			preparedStatement.setInt(1, Integer.parseInt(dataSet));
			resultSet = preparedStatement.executeQuery();
			
			if( resultSet.next()) {
				numberOfMessages = resultSet.getInt(1);
				message = " Login Successful ";
			} else {
				message = " Login Failed ";
			}
		} catch (Exception e) { 
		}
		return numberOfMessages;
	}
	public String getUser( String username, String password ) throws Exception {
		String message = "No message";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT * from user WHERE id=? AND password =?;");
			preparedStatement.setInt(1, Integer.parseInt(username));
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			
			if( resultSet.next()) {
				userID = Integer.parseInt(username);
				message = " Login Successful ";
			} else {
				message = " Login Failed ";
			}
		} catch (Exception e) { 
		}
		return message;
	}
	
	public boolean subscribeDataSet( String dataSet ) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
	//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("INSERT INTO dataset_consumer SET dataset_id=?, consumer_id=(SELECT id FROM consumer WHERE user_id=?);");
			preparedStatement.setInt(1, Integer.parseInt(dataSet));
			preparedStatement.setInt(2, userID);
			int affectedRows = preparedStatement.executeUpdate();
			return true;
		} catch (Exception e) { 
			return false;
		}
	}
	
	public int addNewDataSet (String name, String description , String model) {
		try {
			int dataSetId = 0;
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
	//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			//Insert into dataset table
			preparedStatement = connect.prepareStatement("INSERT INTO dataset SET name=?, description=?, pricing_model=?;");
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, description);
			preparedStatement.setString(3, model);
			int affectedRows = preparedStatement.executeUpdate();
			
			preparedStatement = connect.prepareStatement("SELECT * FROM dataset WHERE name=? AND description=?;");
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, description);
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()) {
				dataSetId = resultSet.getInt("id");
			}
			//Insert into dataset_publisher table
			preparedStatement = connect.prepareStatement("INSERT INTO dataset_publisher SET dataset_id=?, publisher_id=(SELECT id FROM publisher WHERE user_id=?);");
			preparedStatement.setInt(1, dataSetId);
			preparedStatement.setInt(2, userID);
			affectedRows = preparedStatement.executeUpdate();
			
			
			return dataSetId;
		} catch (Exception e) { 
			return -1;
		}
	}
	public boolean unsubscribeDataSet ( String dataSet ) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
	//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("DELETE FROM dataset_consumer WHERE dataset_id=? AND consumer_id=(SELECT id FROM consumer WHERE user_id=?);");
			preparedStatement.setInt(1, Integer.parseInt(dataSet));
			preparedStatement.setInt(2, userID);
			int affectedRows = preparedStatement.executeUpdate();
			return true;
		} catch (Exception e) { 
			return false;
		}
	}
	
	public List<DataSet > getDataSets() {
		List<DataSet > dataSets = new ArrayList<DataSet>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
	//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			
			statement = connect.createStatement();
			resultSet = statement.executeQuery("SELECT id, name , description from dataset;");
			while( resultSet.next()) {
				DataSet current = new DataSet(resultSet.getInt("id"),resultSet.getString("name"),resultSet.getString("description"));
				dataSets.add( current);
			}
		} catch (Exception e) { 
			
		}
		return dataSets;
	}
	public int getPublishConsumption( String dataSet) {
		String message;
		int numberOfMessages = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
	//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT COUNT(quantity) FROM consumption_log WHERE dataset_id = ? AND publisher_id = (SELECT id FROM publisher WHERE user_id=?) AND timestamp >= ?;");
			preparedStatement.setInt(1, Integer.parseInt(dataSet));
			preparedStatement.setInt(2, userID);
			preparedStatement.setLong(3, System.currentTimeMillis()-1000);
			resultSet = preparedStatement.executeQuery();
			
			if( resultSet.next()) {
				numberOfMessages = resultSet.getInt(1);
				if(numberOfMessages>=100){
					return 0;
				}
				message = " Login Successful ";
			} else {
				message = " Login Failed ";
			}
		} catch (Exception e) { 
		}
		return numberOfMessages;
	}
	public List getPublishers() {
		List<Integer> dataSetList = new ArrayList<Integer>();
		
		List allDetails = new ArrayList();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
		//	connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT dataset_id from dataset_publisher WHERE publisher_id=(SELECT id FROM publisher WHERE user_id=?);");
			preparedStatement.setInt(1, userID);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				dataSetList.add(resultSet.getInt(1));
			}
			int i, numberOfDataSets;
			numberOfDataSets = dataSetList.size();
			
			for(i=0;i<numberOfDataSets;i++) {
				List dataSetDetails = new ArrayList();
				PubData current = new PubData();
				
				int currentDataSet = dataSetList.get(i);
			//	current.datasetID = currentDataSet;
		//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
				preparedStatement = connect.prepareStatement("SELECT name from dataset WHERE id=?;");
				preparedStatement.setInt(1, currentDataSet);
				resultSet = preparedStatement.executeQuery();
				if(resultSet.next()) {
					String dataset_name = resultSet.getString(1);
					//dataSetDetails.clear();
					dataSetDetails.add(currentDataSet);
					dataSetDetails.add(dataset_name);
					
					//current.datasetName = dataset_name;
					preparedStatement = connect.prepareStatement("SELECT COUNT(consumer_id) from dataset_consumer WHERE dataset_id=?;");
					preparedStatement.setInt(1, currentDataSet);
					resultSet = preparedStatement.executeQuery();
					if(resultSet.next()) {
						
						int numberOfConsumers = resultSet.getInt(1);
						dataSetDetails.add(numberOfConsumers);
						//current.numberOfSubscribers = numberOfConsumers;
						allDetails.add(dataSetDetails);
					}
				}
			}
		} catch (Exception e) { 
			
		}
		return allDetails;
	}
	public String getUserName( String user_id) {
		String userName = "";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
		//	connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT name from user WHERE id=?;");
			preparedStatement.setInt(1, Integer.parseInt(user_id));
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()) {
				userName = resultSet.getString(1);
			}
		} catch (Exception e) {
			
		}
		
		return userName;
	}
	public List getPublisherNameAndSubscriberNumber( String dataSet) {
		List returnInfo = new ArrayList();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT publisher_id from dataset_publisher WHERE dataset_id=?;");
			preparedStatement.setInt(1, Integer.parseInt(dataSet));
			resultSet = preparedStatement.executeQuery();
			
			if( resultSet.next()) {			
				int publisherID = resultSet.getInt(1);
			//	connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
				preparedStatement = connect.prepareStatement("SELECT name from user WHERE id=?;");
				preparedStatement.setInt(1, publisherID);
				resultSet = preparedStatement.executeQuery();
				if( resultSet.next()) {	
					String publisherName = resultSet.getString(1);
					returnInfo.add(publisherName);
					
			//		connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
					preparedStatement = connect.prepareStatement("SELECT COUNT(consumer_id) from dataset_consumer WHERE dataset_id=?;");
					preparedStatement.setInt(1, Integer.parseInt(dataSet));
					resultSet = preparedStatement.executeQuery();
					
					if( resultSet.next()) {	
						int numberOfSubscribers = resultSet.getInt(1);
						returnInfo.add(numberOfSubscribers);
						
						preparedStatement = connect.prepareStatement("SELECT pricing_model from dataset WHERE id=?;");
						preparedStatement.setInt(1, Integer.parseInt(dataSet));
						resultSet = preparedStatement.executeQuery();
						
						if( resultSet.next()) {
							String model = resultSet.getString(1);
							returnInfo.add(model);
						}
					}
				}
			}
		} catch (Exception e) { 
			
		}
		return returnInfo;
	}
	
	public List<Integer > getUserDataSets() {		
		List<Integer > dataSetList = new ArrayList<Integer>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT dataset_id from dataset_consumer WHERE consumer_id=(SELECT id FROM consumer WHERE user_id=?);");
			preparedStatement.setInt(1, userID);
			resultSet = preparedStatement.executeQuery();
			
			while( resultSet.next()) {			
				dataSetList.add( resultSet.getInt("dataset_id"));
			}
		} catch (Exception e) { 
			
		}
		return dataSetList;
	}
	public String getPubModel(String dataSet) {
		// TODO Auto-generated method stub
		
		String pubModel = "";
		try {
		//	Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
		//	connect = DriverManager.getConnection("jdbc:mysql://localhost/sapfeed","root","");
			preparedStatement = connect.prepareStatement("SELECT pricing_model from dataset WHERE id=?;");
			preparedStatement.setInt(1, Integer.parseInt(dataSet));
			resultSet = preparedStatement.executeQuery();
			
			if( resultSet.next()) {			
				pubModel = resultSet.getString(1);
			}
		} catch (Exception e) { 
			e.printStackTrace();
		}
		
		return pubModel;
	}
}
